"""The application's model objects"""
import sqlalchemy as sa
from sqlalchemy import types
from pylons import session
from pylons.controllers.util import abort, redirect
import logging
log = logging.getLogger(__name__)

from meta import Session, Base
import datetime

def init_model(engine):
    """Call me before using any of the tables or classes in the model"""
    Session.configure(bind=engine)

def empty_database():
    log.info("Deteling Database rtm")
    meta.Session.execute("DROP DATABASE rtm;")
    meta.Session.commit()
    log.info("Database rtm dropped")
    log.info("Creating database rtm")
    meta.Session.execute("CREATE DATABASE rtm;")
    meta.Session.commit()
    meta.Session.execute("use rtm;")
    meta.Session.commit()
    log.info("Database rtm created")
    return True


class User(Base):
    __tablename__ = "user"
    
    id = sa.Column(types.Integer, primary_key=True)
    email = sa.Column(types.Unicode(255))
    password = sa.Column(types.Unicode(64))
    first_name = sa.Column(types.Unicode(255))
    last_name = sa.Column(types.Unicode(255))
    when_created = sa.Column(types.DateTime())
    last_login = sa.Column(types.DateTime())

    
    def create_default_user_admin(self):
        admin = User()
        admin.when_created = datetime.datetime.now()
        admin.email = 'admin@admin.com'
        admin.password = 'admin'
        admin.last_name = 'admin'
        admin.first_name = 'admin'
    
        Session.add(admin)
        Session.commit()
        
        admin = User()
        admin.when_created = datetime.datetime.now()
        admin.email = 'bastian'
        admin.password = 'bastian'
        admin.last_name = 'bastian'
        admin.first_name = 'bastian'
    
        Session.add(admin)
        Session.commit()
        
        admin = User()
        admin.when_created = datetime.datetime.now()
        admin.email = 'fukakai'
        admin.password = 'fukakai'
        admin.last_name = 'fukakai'
        admin.first_name = 'fukakai'
    
        Session.add(admin)
        Session.commit()

class UserCreation(Base):
    __tablename__ = "user_project"
    
    id = sa.Column(types.Integer, primary_key=True)
    name = sa.Column(types.Unicode(255))     
    description = sa.Column(types.Text)
    us_id = sa.Column(types.Integer)
    when_created = sa.Column(types.DateTime())

class ChatMessages(Base):
    __tablename__ = "chat_message"
    
    id = sa.Column(types.Integer, primary_key=True)
    us_id = sa.Column(types.Integer)
    when_created = sa.Column(types.DateTime())
    message = sa.Column(types.Unicode(500)) 
        
class ChatMessagesToUser(Base):
    __tablename__ = "chat_message_to_user"
    
    id = sa.Column(types.Integer, primary_key=True)
    us_id = sa.Column(types.Integer)
    id_mess = sa.Column(types.Integer)
    @classmethod
    def get_messages_from_the_day(self):
        
        GET_MESSAGE = """
            SELECT cm.when_created as when_created, cm.id as mess_id, (SELECT last_name FROM user WHERE id = cm.us_id) as last_name  , cm.message as message 
            FROM  chat_message as cm
            WHERE cm.when_created >= :time
         """
        messages = Session.execute(GET_MESSAGE, {'us_id' : session['user'], 'time' : session['chat_time']} ) 
        
        return messages
       
    
class ChatWhoIsConnected(Base):
    __tablename__ = "chat_who_is_connected"
    id = sa.Column(types.Integer, primary_key=True)
    us_id = sa.Column(types.Integer)
    connected = sa.Column(types.Integer)